# Chapter 17. MySQL and mSQL Programs and Utilities

#### Contents:

MySQL Utilities
mSQL Utilities

Both MySQL and mSQL come prepackaged with a wealth of programs and utilities to make interacting with the database server easier. Some of these programs are used by the end user to read and write from the database, while others are meant for the database administrator to maintain and repair the database as a whole.

## 17.1. MySQL Utilities

isamchk |

    isamchk [options]table [table...]

Performs operations on the database table files themselves (called ISAM files for Indexed Sequential Access Method). This utility is used to check and repair the files, as well as report information about them. You must provide the correct path to the ISAM file you wish to examine. The default location for the ISAM files is /usr/local/var/databasename/tablename.ISM.

#### Options

-?, --help

  Display usage information.

-# debuglevel, --debug=debuglevel

  Set the debugging level to `debuglevel`. The debugging library used by MySQL has many options. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html. The most common set of options is `d:t:o,/tmp/debugfile`.

-a, --analyze

  Analyze the distribution of keys within a table and make adjustments if performance would be improved.

-d, --description

  Display short description of a table.

-e, --extend-check

  Perform additional checks on the integrity of a table. With this option, you can be absolutely sure that the table is not damaged.

-f, --force

  Overwrite without warning files that already exist. Also automatically recover damaged tables without notice.

-i, --information

  Display full statistics the table(s) being checked.

k=number, --keys-used=number

  Update only `number` of keys used. This option is used mainly to disable the keys `(-k=0)` to speed up other operations on the table such as a bulk load.

-l, --no-symlinks

  Do not repair a table that is a symlink.

-q, --quick

  Speed up repair by not examining the data file.

-r, --recover

  Perform general repair on the table. Does not fix duplicate keys which are supposed to be unique.

-o, --safe-recover

  Use an older, slower method of recovery that can repair some things that "-r" will miss.

-O, --set-variable

  Set an option variable. See Chapter 16, "MySQL and mSQL System Variables", for a full list of usable variables.

-s, --silent

  Display only errors

-S, --sort-index

  Sort the index block of the table.

-R=index, --sort-records=index

  Sort the records of the table by `index` within the table.

-u, --unpack

  Decompress a file packed with pack_isam.

-v, --verbose

  Display extra information.

-V, --version

  Display version information.

-w, --wait

  If the table is locked, wait for it to be unlocked. Without this option, ismchk will exit if it encounters a locked table.

isamlog |

    isamlog [options] [logfile] [table]

Displays information about ISAM logs. An ISAM log is generated if the MySQL server is started with the -`log-isam` option. The information in the ISAM log can be used to recover damaged tables using the -r option. However, modifying the data files directly can be dangerous and you should always back up your data before doing so.

#### Options

-?, --help

  Display usage information

-# debuglevel

  Set debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html.

-c number

  Examine only the last `number` commands.

-f number

  Maximum number of open files. When repairing large logs isamlog can have a number of tables open at once. If you consistently run out of file descriptors on your system, this option will limit the number of files isamlog uses. Instead of using more files, it will juggle data between the open files and memory, resulting in slower operation.

-F directory

  Directory containing the ISAM log file.

-i

  Display additional information.

-o number

  Offset number commands before examining the log.

-p

  Remove components from the path.

-r

  Ignore errors while examining log. This option allows you to recover all of the information within a log file.

-R datafile recordnumber

  Open an ISAM data file (a file ending with .ISM) and retrieve the data at `recordnumber`.

-u

  Update the tables using the log information.

-v

  Display extra information about the process.

-V

  Display version information.

-w file

  Write all records found using -R to a file.

mysql |

    mysql [options] [database]

The MySQL command line monitor. This program is the most basic way to communicate with the MySQL server. SQL commands can be typed directly on the command line and the results are displayed on the screen. If database is supplied, it is automatically selected as the current database.

The command line monitor works much like a bash shell, because it uses the same `GNU` readline function that bash uses. For example, you can complete a word by using the tab key, press Ctrl-a to jump to the start of the current line or Ctrl-e to jump to the end, press Ctrl-r to perform a reverse search, and use the up arrow to retrieve the previous command.

Statements can continue over multiple lines and are not acted upon until a command is given. When using full word commands (go, print, etc.) the command must be entered on a line by itself. Escape character commands (\g, \p, etc.) can be used at the end of any line. In addition, a semicolon can be used to end an SQL statement just like \g.

#### Commands

help, ?, \h

  Display the list of commands.

clear, \c

  Clear (ignore) the current statement.

edit, \e

  Edit the current statement using the default editor.

exit.

  Exit the program.

go, \g, ;

  Send the current statement to the database server.

ego, \G

  Send the current statement to the server and display the results vertically.

print, \p

  Display the current statement.

quit, \q

  Same as exit.

rehash, \#

  Rebuild the index of completion terms.

status, \s

  Display status information about the server and the current session.

use, \u

  Select another database.

#### Options

-?, --help

  Display usage information.

-# debuglevel. -debug=debuglevel

  Set the debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html

-A, --no-auto-rehash.

  Do not automatically rehash database information.

-B, --batch

  Print results in `batch' mode. This provides minimally formatted output which can easily be used in other database applications.

-e statement, --execute=statement

  Execute the given statement and quit. Automatically implies -B.

-f, --force

  Do not stop processing when an SQL error is encountered.

-h host, --host=host

  Connect to the database server on the specified host.

-n, --unbuffered

  Do not buffer output between queries.

-O variable=value, --set-variable variable=value

  Set an option variable. See Chapter 16, "MySQL and mSQL System Variables" for a full list of usable variables.

-p [password], --password[=password]

  The password used to connect to the database server. If this option is used without a value, the password is asked from the command line.

-P port, --port=port

  The port number used to connect to the database server.

-q, --quick

  Display output as it comes from the server. If you suspend your terminal while using this option, the server could pause.

-r. -raw

  Display output without any conversion. Only useful in conjunction with -B.

-s, --silent

  Suppress some output.

-S file, --socket=file

  The Unix socket file used to connect to the database server.

-t, --table

  Display output in table format.

-T, --debug-info

  Display debugging information when the program exits.

-u username, --user=username

  Username used for connection with the database.

-v, --verbose

  Display extra output.

-V, --version

  Display version information

-w, --wait

  Wait and attempt connection later if unable to connect to the database server.

mysqlaccess |

    mysqlaccess [options] [host] user database

Displays and modifies access rights for the MySQL server. You may examine the rights of users for any database and in connection with any host. Unix shell wildcards `*' and `?' may be used to match multiple hosts, users and databases. All actions are performed on a copy of the actual grant tables until a `mysqlaccess --commit` command is sent.

#### Options

-?, --help

  Display usage information.

-b, --brief

  Display results as a brief single line table.

--commit

  Move changes from temporary table to the actual grant tables. You must run mysqladmin reload before the changes will take effect.

--copy

  Renew the temporary table from the actual grant tables.

-d database, --db=database

  The database to which to connect.

--debug=debuglevel

  Set the debugging level (0 through 3).

-h host, --host=host

  The host whose access rights are examined.

--howto

  Usage examples for the program.

-H host, --rhost=host

  Connect to a database server on a remote host.

--old-server

  Connect to a pre-3.21 MySQL server.

-p password, --password=password

  Check the password of the user being examined.

--plan

  Display suggestions for future releases.

--preview

  Show difference between temporary table and actual grant tables.

-P password, --spassword=password

  Administrative password used to access the grant tables.

--relnotes

  Display the release notes for the program.

--rollback

  Undo the changes made to the temporary table.

-t, --table

  Display results in full table format.

-u username, --user=username

  User to be examined.

-U username. -superuser=username

  Administrative username used to access the grant tables.

-v, --version

  Display version information.

mysqladmin |

    mysqladmin [options] command [command...]

Performs operations that affect the database server as a whole. This utility is used to shutdown the database server, add and delete entire databases, and other administrative functions.

#### Commands

create database

  Create a new database.

drop database

  Remove and destroy a database.

extended-status

  Report a fuller status from the server than the status command.

flush-hosts

  Send all buffered information to the clients.

flush-logs

  Flush all buffered log data.

flush-privileges

  Same as reload.

flush-status

  Clear the status variables.

flush-tables

  Commit all buffered table operations.

kill thread-id [thread-id...]

  Kill one or more mysqld server threads.

password password

  Set the administration password for the database server.

ping

  Check if the MySQL server is alive.

processlist

  Show the active mysqld server threads.

reload

  Reload access information from the grant tables.

refresh

  Perform all buffered table operations and reopen the log files.

shutdown

  Shutdown the database server.

status

  Report the status of the server.

variables

  Display the system variables used by the server.

version

  Display the version number of the server.

#### Options

-?, --help

  Display usage information.

-# debuglevel, --debug=debuglevel

  Set the debugging level. See isamchk for more information.

-f, --force

  Drop tables without confirmation. Also, do not quit if an error is encountered.

-h host, --host=host

  Connect to the MySQL server on the given host.

-i seconds, --sleep=seconds

  Perform the commands repeatedly, sleeping the given number of seconds between each run.

-p [password], --password=[password]

  Password used to connect to database server. If this options is used without an argument, the password is asked from the command line.

-P port, --port=port

  Port numbed used to connect to a remove database server.

-s, --silent

  Do not give an error if unable to connect to the database server.

-S file, --socket=file

  The Unix socket used to connect to the local database server.

-u username, --user=username

  User used to connect to the database server.

-V, --version

  Display version information for the mysqladmin program.

mysqlbug |

    mysqlbug

Report a bug in a MySQL program or utility. This program collects information about your MySQL installation and sends a detailed problem report to the MySQL team.

mysqld |

    mysqld [options]

The MySQL server daemon. All other programs interact with the database through this server, so it should be left running at all times (except when down for maintenance). The daemon is usually started from a script called safe_mysqld. This script sets the appropriate environment variables and launches mysqld with the necessary arguments.

#### Options

-?, -I, --help

  Display usage information.

-# debuglevel, --debug=debuglevel

  Set the debugging level. See isamchk for more information.

-b directory, --basedir=directory

  The base directory used to determine all other directories.

--big-tables

  Allow large result sets by saving temporary results in a file.

--bind-address=ip-number

  The IP address the server binds to.

-h directory, --datadir=directory

  The directory containing the database data files.

-l [logfile], --log[=logfile]

  Log various information, including connections and errors. If no argument is provided, hostname.log is used as the log file, where `hostname` is the name of the server machine.

--log-isam[=logfile]

  Log changes to the data (ISAM) files. If no argument is provided, isam.log is used as the log file. The log generated by this option can be read and manipulated with the isamlog utility.

--log-update[=number]

  Log database updates. The log file is named hostname.num, where `hostname` is the name of the server machine and `num` is the argument to the option or a unique number if no argument is given.

-L=language, --language=language

  The language (English, French, etc.) for the server to use.

-n, --new

  Enable new (and possibly unsafe) routines.

-o, --old-protocol

  Use the 3.20.x protocol.

-O variable=value, --set-variable variable=value

  Set an option variable. See Chapter 16, "MySQL and mSQL System Variables" for a full list of usable variables.

--pid-file=file

  The name of the file containing the process ID (PID) of the running server. The default value is `hostname.pid` where `hostname` is the name of the server machine.

-P port, --port=port

  The network port number to use.

--secure

  Enable network security checks which reduce database performance.

--skip-name-resolve

  Use only IP numbers (not names) for connections. This increases network performance.

--skip-networking

  Disable network connections and allow only local access.

--skip-thread-priority

  Give all threads the same priority.

-S, --skip-new

  Do not enable new (and possibly unsafe) routines.

-Sg

  Disable access checking and allow all users full access to all databases.

-Sl

  Do not perform thread locking.

--socket=file

  The filename of the Unix socket

-T, --exit-info

  Display debugging information when shutting down the server

--use-locking

  Enable thread locking

-v, -V, --version

  Display version information

mysqldump |

    mysqldump [options] database[table]

Outputs the contents of the given database (or table within a database) as a series of ANSI SQL commands. This command is handy for breaking up a database; use the -1 and -opt options.

#### Options

-?, --help

  Display usage information.

-# debuglevel, --debug=debuglevel

  Set the debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html.

--add-drop-table

  Include a DROP TABLE statement before every CREATE TABLE.

--add-locks

  Add LOCK TABLE statements around the data insertion statements.

--allow-keywords

  Output column names that are also reserved keywords. This is not normally desirable as the column may conflict with the keyword.

-c, --compleat-insert

  Output complete INSERT statements.

-C, --compress

  Use data compression in the connection with the server.

--delayed

  Use the INSERT DELAYED statement to insert rows.

-d, --no-data

  Do not dump the data, just the table creation statements.

-e, --extended-insert

  Uses the multiple-value form of the INSERT statement which can speed up data insertion.

-f, --force

  Do not exit if an error is encountered.

-F, --flush-logs

  Flush buffered log data before dumping the table(s).

--fields-enclosed-by=delimeter

  When dumping with -T, this delimiter is placed on both sides of each field.

--fields-escaped-by=delimeter

  When dumping with -T, this delimiter is placed before any special character as an escape character.

--fields-terminated-by=delimeter

  When dumping with -T, this delimiter is used after every field (default is tab).

-h hostname, --host=hostname

  Connect to a database server on a remote host.

-l, --lock-tables

  Lock the tables before dumping.

--lines-terminated-by=delimeter

  When dumping with -T, this delimiter is used after every line.

-t, --no-create-info

  Do not dump the table creation statements, just the data.

-O variable=value, --set-variable variable=value

  Set an option variable. See Chapter 16, "MySQL and mSQL System Variables" for a full list of usable variables.

--opt

  Adds the most common and useful command line options: --add-drop-table, --add-locks, --extended-insert, --quick, and --use-locks.

-p [password], --password[=password]

  The password used to connect to the database server. If no argument is given, the password is asked from the command line.

-P port, --port=port

  The port used to connect to a remove database server.

-q, --quick

  Display all data immediately, without buffering.

-S file, --socket=file

  The Unix socket used to connect to the local server.

-T directory, --tab=directory

  Generates a tab-separated file with the table data along with a file containing the SQL table creation statements. The files are outputted to the given directory.

-u username, --user=username

  Username used to connect to the database server.

-v, --verbose

  Display information about the state of the process while dumping the data.

-V, --version

  Display version information.

-w statement, --where=statement

  Outputs only the rows that satisfy the given SQL WHERE clause.

mysqlimport |

    mysqlimport [options] database [file]

Reads a file of data in a variety of common formats (such as comma delimited or fixed width) and inserts the data into a database. A table with the same name as the file must exist in the database with enough columns of the appropriate type to store the data.

#### Options

-?, --help

  Display usage information.

-# debuglevel, --debug=debuglevel

  Set the debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html.

-d, --delete

  Delete all data currently in the table before inserting the new data.

-f, --force

  Do not exit if an error is encountered.

--fields-terminated-by=string

  Indicates that the fields in the data file are terminated by a string.

--fields-enclosed-by=string

  Indicates that the fields in the data file are enclosed by a string.

--fields-optionally-enclosed-by=string

  Indicates that the fields in the data file could also be enclosed by another string.

--fields-escaped-by=string

  The string used as escape characters in the data file.

-h hostname, --host=hostname

  Connect to a database server on a remote host.

-I, --ignore

  Ignore the new data if it conflicts with an existing unique key.

-l, --lock-tables

  Lock the tables before inserting the data.

-p [password], --password[=password]

  Password used to connect to the database server. If no argument is given, the password is asked from the command line.

-P port, --port=port

  Port used to connect to a remove database server.

-r, --replace

  If the new data conflicts with an existing unique key, replace the old data.

-s, --silent

  Suppress some output.

-S file, --socket=file

  The Unix socket used to connect to the local database server.

-u username, --user=username

  The username used to connect to the database server.

-v, --verbose

  Display information about the process while inserting the data.

-V, --version

  Display version information.

mysqlshow |

    mysqlshow [options] [database] [table] [field]

Displays the layout of the requested database, table or field. If no argument is given, a list of all of the databases is given. With one argument the layout of the given database is show. With two arguments, a table within the database is displayed. If all three arguments are present, the information about a specific field within a table is presented.

#### Options

-?, --help

  Display usage information.

-# debuglevel, --debug=debuglevel

  Set the debugging level. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html.

-h hostname, --host=hostname

  Connect to a remote database server.

-k, --keys

  Display the keys of a table.

-p [password], --password]=password]

  Password used to connect to the database server. If no argument is given, the password is asked from the command line.

-P port, --port=port

  Port used to connect to a remote database server.

-S file, --socket=file

  The Unix socket used to connect to the local database server.

-u username, --user=username

  Username used to connect to the database server.

-V, --version

  Display version information.

Copyright © 2001 O'Reilly & Associates. All rights reserved.